import os
import json
from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
import pandas as pd
engine = create_engine(
   'mysql+mysqlconnector://root:Bz_202501@bj-cdb-ckq2r8ro.sql.tencentcdb.com:25622/bz_system',
    pool_recycle=3600,
    echo=False,  # 将echo从True改为False以关闭SQL日志
    isolation_level="READ COMMITTED",
    pool_pre_ping=True
)

root_path = '/mnt'
folder_list = ['slebioage','sleep_noon_to_noon','sleep_episodes','daily_summary','pwf_daily','hrv_daily','near_real_time','fitness_event','mental_daily']
folder_date_flag = {'fitness_event': 'startDate','sle_bioage': 'endDate','sleep_noon_to_noon': 'startDate', 'daily_summary': 'startDate', 'hrv_daily': 'startDate','mental_daily': 'startDate', 'pwf_daily': 'startDate','near_real_time':'endDate'}
date_list = [
    (datetime.now() - timedelta(days=i)).strftime('%Y-%m-%d')
    for i in range(1, 8)
]

user_dict = {
    "David": "975783c2-ed1e-4d66-931a-db11e92415ba",
    "王龙": "e66935eb-faec-47d4-bfb9-c6584ecbde62",
    "郭晓鹏": "1e8733d0-6426-46e4-82e2-a54d2af426d1",
    "毕秀静": "7a7670bb-7f04-46c5-bd97-dcae13231ccf",
    "张颖": "f6e3a514-75ce-46c5-898c-83478c965edb",
    "石雅俊": "ae12c612-d1a9-4154-b5cf-af5ac6000d27",
    "孙格格": "526135ee-aceb-4902-9c61-6ca921811ca6",
    "李洋": "cdebfcd6-3fef-4315-b960-3b27e208662f",
    "向先火": "06afd291-cfc4-4d2a-bc82-0de2e185a554",
    "矦佳乐": "64375b0c-f59c-4ea5-ac71-acdc1f8ec70e",
    "于庚辰": "c281f805-bfb7-4645-a256-bd5a7bff106e",
    "大左": "6529e607-d8e1-41c2-8f49-bae0f78cb8ce"
}



def prepare_result_dict():
    """
    初始化结果字典
    """
    result = {}
    for user_name, profile_id in user_dict.items():
        result[user_name] = {}
        for date1 in date_list:
            result[user_name][date1] = {
                'total_sleep_time': -1,
                'circadian_compliance': -1,
                'rem_sleep': -1,
                'deep_sleep': -1,
                'awake': -1,
                'disturbances': -1,
                'rem_sleep_target': -1,
                'sleep_episodes': -1,
                'naps': -1,
                'rests': -1,
                'core_sleep': -1,
                'sleep_restoration': -1,
                'core_sleep_ratio': -1,
                'sleep_regularity': -1,
                'sleep_onset_latency': -1,
                'sleep_efficiency': -1,
                'chronic_stress_elevation': -1,
                'daily_stress_score': -1,
                'total_fitness_calories': -1,
                'total_sessions': -1,
                'total_session_duration': -1,
                'total_calories': -1,
                'sedentary_time': -1,
                'intensity_minutes': -1,
                'steps': -1,
                'average_lf_lh_ratio': -1,
                'average_sdnn': -1,
                'average_rmssd': -1,
                'mean_large_artery_stiffness': -1,
                'mean_heart_blood_supply': -1,
                'mean_heart_muscle_strength': -1,
                'average_heart_rate': -1,
                'max_heart_rate': -1,
                'min_heart_rate': -1,
                'resting_heart_rate': -1,
                'wake_after_sleep_onset': -1,
                'rem_onset_latency': -1,
                'sleep_momentum': -1
            }
    return result
result = prepare_result_dict()
def load_data_from_sleep_noon_to_noon(f, user_name, date_flag):
    """
    处理 sleep_noon_to_noon 文件夹的数据
    """
    data = json.load(f)
    data_list = data.get('data')
    if isinstance(data_list, list) and len(data_list) > 0:

        coreSleep_start = data_list[0].get('summary', {}).get('coreSleep', {}).get('startTime')
        coreSleep_end = data_list[0].get('summary', {}).get('coreSleep', {}).get('endTime')
        result[user_name][date_flag]['toal_sleep_time'] = (coreSleep_end - coreSleep_start)/3600 if coreSleep_start and coreSleep_end else -1
        result[user_name][date_flag]['circadian_compliance'] = data_list[0].get('summary', {}).get('ratings', {}).get('circadianCompliance', {}).get('value', -1)
        ratings = data_list[0].get('summary', {}).get('ratings', {})
        result[user_name][date_flag]['rem_sleep'] = ratings.get('remDuration', {}).get('value', -3600)/3600
        result[user_name][date_flag]['deep_sleep'] = ratings.get('deepDuration', {}).get('value', -3600)/3600
        result[user_name][date_flag]['disturbances'] = ratings.get('disturbances', {}).get('value', -1)
        result[user_name][date_flag]['rem_sleep_target'] = ratings.get('remNeededToday', {}).get('value', -3600)/3600
        result[user_name][date_flag]['sleep_episodes'] = ratings.get('schlafDuration', {}).get('value', -3600)/3600
        result[user_name][date_flag]['naps'] = ratings.get('napDuration', {}).get('value', -3600)/3600
        result[user_name][date_flag]['rests'] = ratings.get('restDuration', {}).get('value', -3600)/3600
        result[user_name][date_flag]['core_sleep'] = ratings.get('coreTotalSleepTime', {}).get('value', -3600)/3600
    



def gather_data_from_folder(folder_df,user_name,folder_name):
    ##准备拿某个人某个文件夹下的几天的数据出来
    #print(f"正在处理文件夹数据: {folder_df}")
    for index, row in folder_df.iterrows():
        profile_id = row['profile_id']
        date_flag = row['date_flag']
        json_name = row['json_name']
        times_tamp = row['times_tamp']

        file_path = os.path.join(root_path, folder_name, row['date_folder'], profile_id, json_name)
        #print(f"处理文件: {file_path}")
        with open(file_path, 'r') as f:
            try:
                # 假设你有多个函数，比如 load_data_from_sleep_episodes, load_data_from_daily_summary 等
                func_name = f"load_data_from_{folder_name}"
                if func_name in globals():
                    func = globals()[func_name]
                    func(f, user_name, date_flag)  # 你可以根据需要传递参数
                else:
                    print(f"未找到处理函数: {func_name}")

                
            except (json.JSONDecodeError, KeyError) as e:
                print(f"Error reading {file_path}: {e}")

def main():
    
    for user_name, profile_id in user_dict.items():
        # 对这个用户遍历所有的文件夹
        for folder_name in folder_list:
            # 先找到近七日每天最晚来的文件
            # print(f"正在处理用户 {user_name} ({folder_name}) 的数据...")
            folder_data = []
            for current_date in date_list:
                data_path = os.path.join(root_path, folder_name, current_date, profile_id)
                if not os.path.exists(data_path):
                    continue
                row = {
                    'profile_id': profile_id,
                    'date_flag': -1,
                    'date_folder':current_date,
                    'json_name': -1,
                    'times_tamp': -1
                }

                for json_name in os.listdir(data_path):
                    json_path = os.path.join(data_path, json_name)
                    if os.path.getsize(json_path) == 0:
                        continue
                    times_tamp = datetime.fromtimestamp(int(json_name.split('_')[1].split('.')[0]) / 1000000).strftime('%Y-%m-%d %H:%M:%S')
                    row['json_name'] = json_name
                    row['times_tamp'] = times_tamp
                    with open(json_path, 'r') as f:
                        try:
                            data = json.load(f)
                            if folder_name == 'sleep_episodes':
                                row['date_flag'] = datetime.fromtimestamp(int(data['header']['startTime'])).strftime('%Y-%m-%d')
                            else:
                                row['date_flag'] = data['header'][folder_date_flag[folder_name]][0:10]
                            folder_data.append(row)
                        except json.JSONDecodeError as e:
                            print(f"文件 {json_name} 解析错误: {str(e)}")
            # 对这几天的数据进行处理，找到每个文件夹每天的最新数据
            if not folder_data:
                continue
            folder_df = pd.DataFrame(folder_data)
            # 只保留date_flag在date_list中的数据
            folder_df = folder_df[folder_df['date_flag'].isin(date_list)]
            # 按时间降序排序
            folder_df = folder_df.sort_values(by='times_tamp', ascending=False)
            #    每个date_flag只保留最新的一条
            folder_df = folder_df.drop_duplicates(subset=['date_flag'], keep='first')
            #print(f"用户 {user_name} ({folder_name}) 的数据处理完成， {folder_df} ")
            gather_data_from_folder(folder_df,user_name,folder_name)
            break
        break
    #print(result['David'])
if __name__ == "__main__":
   main()